
USE master
GO
--Create a database
 IF EXISTS(SELECT name FROM sys.databases
     WHERE name = 'TPI')
     DROP DATABASE TPI
GO
CREATE DATABASE TPI
GO
USE TPI
----------------------------------
--   Tabla Provincias
----------------------------------
CREATE TABLE Provincias (
id_provincia int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
nombre char(60)
)
----------------------------------
--   Tabla Localidad
----------------------------------
CREATE TABLE Localidad (
	id_localidad int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	nombre char(100),
	id_provincia int,
	FOREIGN KEY (id_provincia) REFERENCES Provincias(id_provincia) ON DELETE CASCADE ON UPDATE CASCADE
)
----------------------------------
--   Tabla TipoDocumento
----------------------------------
CREATE TABLE TipoDoc (
	id_tipodoc int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	tipodoc char(25)
)
----------------------------------
--   Tabla EstadoCivil
----------------------------------
CREATE TABLE EstadoCivil (
	id_estadocivil int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	estadocivil char(25)
)
----------------------------------
--   Tabla Clientes
----------------------------------
CREATE TABLE Clientes (
	id_cliente int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	Apellido char(40),
	Nombre char(40),
	id_TipoDoc int,
	NroDoc int,
	domicilio char(160),
	Fecha_Nac datetime,
	id_localidad int,
	telefono int,
	FOREIGN KEY (id_localidad) REFERENCES Localidad(id_localidad) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (id_tipodoc) REFERENCES Tipodoc(id_tipodoc) ON DELETE CASCADE ON UPDATE CASCADE
)
----------------------------------
--   Tabla Empleados
----------------------------------
CREATE TABLE Empleados (
	id_empleado int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	Apellido char(40),
	Nombre char(40),
	id_TipoDoc int,
	NroDoc int,
	domicilio char(160),
	Fecha_Nac datetime,
	id_localidad int,
	telefono int,
	id_estadocivil int,
	FOREIGN KEY (id_localidad) REFERENCES Localidad(id_localidad) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (id_tipodoc) REFERENCES Tipodoc(id_tipodoc) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (id_estadocivil) REFERENCES EstadoCivil(id_estadocivil) ON DELETE CASCADE ON UPDATE CASCADE
)
----------------------------------
--   Tabla NivelDificultad
----------------------------------
CREATE TABLE NivelDificultad (
	id_dificultad int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	nombre char(60)
)
----------------------------------
--   Tabla Objetivos
----------------------------------
CREATE TABLE Objetivos (
	id_objetivo int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	id_cliente int,
	domicilio char(160),
	telefono int,
	id_dificultad int,
	FOREIGN KEY (id_cliente) REFERENCES Clientes(id_cliente) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (id_dificultad) REFERENCES NivelDificultad(id_dificultad) ON DELETE CASCADE ON UPDATE CASCADE
)
----------------------------------
--   Tabla Turno
----------------------------------
CREATE TABLE Turnos (
	id_turno int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	nombre char(50),
	hora_inicio int,
	hora_fin int
)
----------------------------------
--   Tabla Orden De Trabajo
----------------------------------
CREATE TABLE OrdenTrabajo (
	id_orden int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	id_objetivo int,
	id_empleado int,
	id_turno int,
	dia_inicio DATETIME,
	dia_fin DATETIME,
	FOREIGN KEY (id_objetivo) REFERENCES Objetivos(id_objetivo) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (id_turno) REFERENCES Turnos(id_turno) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (id_empleado) REFERENCES Empleados(id_empleado) ON DELETE CASCADE ON UPDATE CASCADE
)
----------------------------------
--   Inserciones
----------------------------------
insert into TipoDoc Select 'DNI' as tipodoc
insert into TipoDoc Select 'Libreta Civica' as tipodoc
insert into TipoDoc Select 'Libreta de Enrolamiento' as tipodoc
----------------------------------
select * from 
